from lxml import etree
QUERY_SELECT = "SELECT {0} t_s.operation , t_s.synchronized_version FROM {1} WHERE {2} and operation != 'N' and operation != 'D';"
QUERY_UPDATE = "UPDATE {0} SET {1} WHERE {2};"
QUERY_INSERT = "INSERT INTO {0} {1} VALUES {2};"

QUERY_UPDATE_SYNCHRONIZED =  "UPDATE {0}_synchronized SET operation = 'N', synchronized_version = {1} where {2};"
QUERY_CONFLICT_SYNCHRONIZED =  "UPDATE {0}_synchronized SET operation = 'C' where {1};"


SERVER = 1
CLIENT = 0

 
class DesingQuery:
	def __init__(self,root,program):
		self.root = root
		self.program = program
		if self.program == SERVER:
			self.typeField = 'fieldServer'
			self.chownTable = 'table_server'
		else:
			self.typeField = 'fieldClient'
			self.chownTable = 'table_client'

	def querySelectForTable(self):
		for table in list(self.root):
			columns = ''
			name = ''
			query = ''
			conditionExpresion = ''
			tableName = table.get(self.chownTable)
			tables = ''.join([tableName,' t ,', tableName , "_synchronized", " t_s"]) 
			for field in list(table):
				columnTable =  field.find(self.typeField)
				name = columnTable.get("name")
				if field.get("key") == "True":
					conditionExpresion = ''.join([conditionExpresion, "t.",name," = ","t_s.",name])
				columns = 't.'.join([columns,name + " , ", ])
			yield QUERY_SELECT.format(columns,tables,conditionExpresion)

	def getKeyField(self):
		"""Busca los campos clave """
		for table in list(self.root):
			keyField = []
			i = 0
			for field in list(table):
				if field.get("key") == "True":
					keyField.append(i)
				i += 1
			yield keyField

	def getFields(self):
		for table in list(self.root):
			yield table

	def numberColumns(self):
		for table in list(self.root):
			yield len(table)

	

	def updateQuery(self, values,fields):
		condition = ''
		table = fields.get(self.chownTable)
		query = ''
		i = -1
		lenValues = len(values)
		for field in fields:
			column = field.find(self.typeField)
			i += 1
			if field.get("key")== "True":
				condition += column.get("name") + " = " + str(values[i]) +  " and "
				continue
			query += column.get("name") + " = '" + str(values[i])	+ "' , "	
		return QUERY_UPDATE.format(table,query[0:len(query)-2],condition[0:len(condition)-4])

		
	def insertQuery(self, values,fields):
		condition = ''
		table = fields.get(self.chownTable)
		i = 0
		setColumns = '( '
		setValues = '( '
		for field in fields:
			column = field.find(self.typeField)
			setColumns += column.get("name") + ' , '
			setValues += "'" + str(values[i]) + "' , "	
			i += 1
		return QUERY_INSERT.format(table,setColumns[0:len(setColumns)-2] + ' )' ,setValues[0:len(setValues)-2] + ' ) ')

	def querySynchronized(self,values,table):
		condition = ''	
		i = -1
		for field in table:
			column = field.find(self.typeField)
			i += 1
			if field.get("key")== "True":
				condition += column.get("name") + " = " + str(values[i]) +  " and "
		return QUERY_UPDATE_SYNCHRONIZED.format(table.get(self.chownTable),int(values[len(values)-1])+ 1, condition[0:len(condition)-5])
	

	def queryConflict(self,values,table):
		condition = ''	
		i = -1
		for field in table:
			column = field.find(self.typeField)
			i += 1
			if field.get("key")== "True":
				condition += column.get("name") + " = " + str(values[i]) +  " and "
		return QUERY_CONFLICT_SYNCHRONIZED.format(table.get(self.chownTable), condition[0:len(condition)-5])
